package benny_driving_ser.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


import benny_driving_ser.ObjectBean.PayCodeBean;
import benny_driving_ser.dao.PayCodeDao;
import benny_driving_ser.util.CalcPayCodeCostUtil;
import benny_driving_ser.util.DBUtil;
import benny_driving_ser.util.FormatUtil;
import benny_driving_ser.util.MD5Util;
import benny_driving_ser.util.PayCodeUtil;


public class PayCodeDaoImpl implements PayCodeDao {

	
	public List<PayCodeBean> queryPayCode(String payCode) {
		
		String sql_query_code = "select " +
				"id," +//1
				"codeSource," +//2
				"payCode," +//3
				"fullPayCode," +//4
				"payType," +//5
				"payCost," +//6
				"wait," +//7
				"addTime," +//8
				"used," +//9
				"times," +//10
				"remainCost," +//11
				"dayOrNight," +//12
				"isActivate " +//13
				"from t_paycode where payCode ="+"'"+payCode+"'";
		
		String sql_outside = "select * from t_paycode where codeSource="+"'"+payCode+"'";
		
		PayCodeBean payCodeBean = null;
		List<PayCodeBean> payCodeBeans = new ArrayList<PayCodeBean>();
		Connection conn = DBUtil.getInstance().openConnection();
		try{
			conn.setAutoCommit(false);
			PreparedStatement pst_query = conn.prepareStatement(sql_query_code);
			ResultSet rs_query = pst_query.executeQuery();
			PreparedStatement pst_outside= conn.prepareStatement(sql_outside);
			ResultSet rs_outside = pst_outside.executeQuery();
			if(rs_query.next()){
				
				payCodeBean = new PayCodeBean(
						rs_query.getString("id"), 
						rs_query.getString("codeSource"), 
						rs_query.getString("payCode"),
						rs_query.getString("fullPayCode"),
						rs_query.getString("payType"),
						rs_query.getString("payCost"),
						rs_query.getString("wait"), 
						rs_query.getString("addTime"), 
						rs_query.getString("used"),
						rs_query.getString("times"), 
						rs_query.getString("remainCost"), 
						rs_query.getString("dayOrNight"),
						rs_query.getString("isActivate"));
				payCodeBeans.add(payCodeBean);
				
				return payCodeBeans;
			}else if(rs_outside.next()){
				payCodeBean = new PayCodeBean(
					rs_outside.getString("id"), 
					rs_outside.getString("codeSource"), 
					rs_outside.getString("payCode"),
					rs_outside.getString("fullPayCode"),
					rs_outside.getString("payType"),
					rs_outside.getString("payCost"),
					rs_outside.getString("wait"), 
					rs_outside.getString("addTime"), 
					rs_outside.getString("used"),
					rs_outside.getString("times"), 
					rs_outside.getString("remainCost"), 
					rs_outside.getString("dayOrNight"),
					rs_outside.getString("isActivate"));
				payCodeBeans.add(payCodeBean);
				return payCodeBeans;
			}
			rs_query.close();
			pst_query.close();
			rs_outside.close();
			pst_outside.close();
			conn.commit();
			
		}catch(SQLException e){
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}finally{
			DBUtil.getInstance().closeConnection(conn);
		}
		return null;
	}

	
	public int updatePayCodeState(String payCode,Connection conn) {
		int state = 0;
		String sql_update = "update t_paycode set used=1,times=times-1,remainCost=payCost*times where payCode="+"'"+payCode+"'";
		String sql_update_outside = "update t_paycode set used=1,times=times-1,remainCost=payCost*times where codeSource="+"'"+payCode+"'";
		try{
			PreparedStatement pst_update = conn.prepareStatement(sql_update);
			PreparedStatement pst_update_outside= conn.prepareStatement(sql_update_outside);
			state = pst_update.executeUpdate();
			pst_update.close();
			if(state==0){
				state = pst_update_outside.executeUpdate();
			}
			pst_update_outside.close();
			return state;
		}catch(SQLException e){
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return 2;
		}
	}
	
	
	public  int checkPayCode(String payCode){
		int state = 0;
		String sql_query = "select count(*) count from t_paycode where ((used = 0 OR (times>=1)) and isActivate = 0) AND payCode = "+"'"+payCode+"'";
		String sql_outside = "select count(*) count from t_paycode where ((used = 0 OR (times>=1)) and isActivate = 0) AND codeSource = "+"'"+payCode+"'";
		Connection conn = DBUtil.getInstance().openConnection();
		try{
			conn.setAutoCommit(false);
			PreparedStatement pst_query = conn.prepareStatement(sql_query);
			ResultSet rs_query = pst_query.executeQuery();
			while(rs_query.next()){
				state = rs_query.getInt("count");
			}
			pst_query.close();
			rs_query.close();
			PreparedStatement pst_outside = conn.prepareStatement(sql_outside);
			ResultSet rs_outside = pst_outside.executeQuery();
			if(state!=1){
				while(rs_outside.next()){
					state = rs_outside.getInt("count");
				}
			}
			pst_outside.close();
			rs_outside.close();
			conn.close();
		}catch(SQLException e){
			e.printStackTrace();
			try {
				conn.rollback();
				conn.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			return 2;
		}
		return state;
	}
	
	
	public int checkPayCode2(String payCode){
		int state = 0;
		String sql_query = "select used,times,isActivate from t_paycode where payCode = "+"'"+payCode+"'" +"OR codeSource = "+"'"+payCode+"'";
		Connection conn = DBUtil.getInstance().openConnection();
		try{
			PreparedStatement pst_query = conn.prepareStatement(sql_query);
			ResultSet rs_query = pst_query.executeQuery();
			while(rs_query.next()){
				int used = rs_query.getInt("used");
				int times = rs_query.getInt("times");
				int isActivate = rs_query.getInt("isActivate");
				if(isActivate!=0){
					if(used!=0){
						if(times<=0){
							System.out.println("支付码已经用过了...");
							state = 2;
							return state;
						}
					}else{
						System.out.println("支付码可用...");
						return state;
					}
				}else{
					state = 1;
					System.out.println("支付码未激活..");
					return state;
				}
			}
			rs_query.close();
			pst_query.close();
			conn.close();
		}catch(SQLException e){
			e.printStackTrace();
		}
		return state;
	}
	
	
	public int insertPayCode(String codesource,	String paytype,String paycost,String wait,String times,	String dayOrNight,String isActivate){
		int num = 0;
		String sql_insert  = "insert into t_paycode(" +
				"codeSource," +//填写1
				"fullPayCode," +//生成2
				"payCode," +//生成3
				"payType," +//填写4
				"payCost," +//填写5
				"wait," +//填写6
				"addTime," +//生成7
				"used," +//默认0 7
				"times," +//默认为1 8
				"remainCost," +//默认为0  9
				"dayOrNight," +//默认为0  10
				"isActivate ) " +//默认为0 11
				"values(?,?,?,?,?,?,now(),?,?,(times*payCost),?,?);";
		Connection conn = DBUtil.getInstance().openConnection();
		try{
			conn.setAutoCommit(false);
			PreparedStatement pst_insert = conn.prepareStatement(sql_insert);
			pst_insert.setString(1, codesource);//1
			pst_insert.setString(2, MD5Util.md5(codesource));//2
			pst_insert.setString(3, PayCodeUtil.creatPayCode(MD5Util.md5(codesource)));//3
			pst_insert.setString(4, paytype);//4
			pst_insert.setString(5, paycost);//5
			pst_insert.setString(6, wait);//6
			//pst_insert.setString(7, FormatUtil.getTimeFormat(System.currentTimeMillis()));//7
			pst_insert.setString(7, "0");//8
			if(Integer.parseInt(times)==1){
				pst_insert.setString(8, "1");//9
			}else{
				pst_insert.setString(8, times);//9
			}
			pst_insert.setString(9, dayOrNight);//10
			pst_insert.setString(10, isActivate);//11
			num = pst_insert.executeUpdate();
			pst_insert.close();
			conn.commit();
			return num;
			
		}catch(SQLException e){
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return 2;
		}finally{
			DBUtil.getInstance().closeConnection(conn);
		}
	}
	
	
	
	public static int insertPayCode2Order(int orderId,String payCode,Connection conn){
		int state = 0,cost;
		String sql_insert = "update t_djpd set paycode_cost=? where id = "+orderId;
		try{
			PreparedStatement pst = conn.prepareStatement(sql_insert);
			pst.setString(1, (cost=CalcPayCodeCostUtil.CalcPayCodeCost(payCode,conn))+"");
			System.out.println("cost:"+cost);
			state = pst.executeUpdate();
			pst.close();
		}catch(SQLException e){
			e.printStackTrace();
			return 2;
		}
		return state;
	}
	
	public static int checkOutSideCode(String payCode,Connection conn){
		int state = 0,activate,used = 0,times = 0;
		PayCodeBean payCodeBean = null;
		String sql_query = "select * from t_paycode where codeSource="+"'"+payCode+"'";
		try {
			PreparedStatement pst = conn.prepareStatement(sql_query);
			ResultSet rs = pst.executeQuery();
			while(rs.next()){
				payCodeBean=new PayCodeBean	(
					rs.getString("id"),//
					rs.getString("codeSource"),//2
					rs.getString("payCode"), //3
					rs.getString("fullPayCode"),//4
					rs.getString("payType"), //5
					rs.getString("payCost"),//6
					rs.getString("wait"), //7
					rs.getString("addTime"),//8
					rs.getString("used"), //9
					rs.getString("times"),//10
					rs.getString("remainCost"),//11
					rs.getString("dayOrNight"),//12
					rs.getString("isActivate"));//13
			}
			rs.close();
			pst.close();
			if(payCodeBean!=null){
				if((activate=Integer.parseInt(payCodeBean.getIsActivate()))==0&&((used=Integer.parseInt(payCodeBean.getUsed()))==0||(times=Integer.parseInt(payCodeBean.getTimes()))>1)){
					state = 1;
					return state;
				}else if(activate==1){
					return 2;
				}else if(used==1||times==0){
					return 3;
				}
			}

		} catch (SQLException e) {
			e.printStackTrace();
		}
		return state;
	}
	
	
	public int activatePayCode(String payCode,int months){
		int state = 0;
		List<PayCodeBean> list = queryPayCode(payCode);
		String sql_update_payCode = "update t_payCode set isActivate = 0 where payCode = "+"'"+list.get(0).getPayCode()+"'";
		Connection conn = DBUtil.getInstance().openConnection();
		try{
			conn.setAutoCommit(false);
			if(list!=null){
				PreparedStatement pst_update_payCode = conn.prepareStatement(sql_update_payCode);
				state = pst_update_payCode.executeUpdate();
				pst_update_payCode.close();
				state = addPayCodeUseLog(list.get(0),0,months,conn);
				conn.commit();
				conn.close();
				return state;
			}
		}catch(SQLException e){
			e.printStackTrace();
			try {
				conn.rollback();
				conn.close();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			return 2;
		}
		return state;
	}
	
	public int addPayCodeUseLog(PayCodeBean payCode,int months,int useType,Connection conn){
		int state = 0;
		String insert_log = "insert into t_paycodelog(payCodeid,useType,addTime,useTime,validTime) values(?,?,?,now(),?)";
		try{
			PreparedStatement pst_insert_log = conn.prepareStatement(insert_log);
			pst_insert_log.setString(1, payCode.getId());
			pst_insert_log.setInt(2, useType);
			pst_insert_log.setString(3, payCode.getAddTime());
			switch(useType){
			case 0:
				pst_insert_log.setString(4, FormatUtil.calcMonth(months));
				break;
			}
			state = pst_insert_log.executeUpdate();
			pst_insert_log.close();
		}catch(SQLException e){
			e.printStackTrace();
			try {
				conn.rollback();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			return 2;
		}
		return state;
	}
	
	
	public  int insertPayCost2Pay(String username,int payCost,int orderid,int driid,Connection conn,double balance){
		int state = 0;
		String  sql_insert = "insert into t_pay(username,je,xtsj,bz,driid,formid,lxid,balance) values(?,?,NOW(),'支付码抵扣金额',?,?,1,?)";
		try{
			PreparedStatement pst_insert = conn.prepareStatement(sql_insert);
			pst_insert.setString(1, username);
			pst_insert.setInt(2, payCost);
			pst_insert.setInt(3, driid);
			pst_insert.setInt(4, orderid);
			pst_insert.setDouble(5, balance);
			state = pst_insert.executeUpdate();
			pst_insert.close();
			return state;
		}catch(SQLException e){
			e.printStackTrace();
		}
		return state;
	}
	
	
	public static void main(String[] args) {
		//System.out.println(queryPayCode("c49217cdc").toString());
		//System.out.println(checkOutSideCode("1392265744604",DBUtil.getInstance().openConnection()));
		//System.out.println(checkPayCode("1392265744604"));
		//System.out.println(checkPayCode("2102580500013027"));
//		System.out.println(queryPayCode("1392623457707").toString());
	}
	
}
